﻿using System.Data;
using System.Data.SqlClient;
using LY.Entity;
using LY.Entity.Entities;
using LY.Entity.PageSearch;
using LY.Entity.SearchEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;



namespace LY.DataAccess.Repository
{
    public class MemberRepository : BaseRepository
    {

        //系统消息列表
        public PagedList<U_Information_EX> QuerySystemInformation(InformationSearch search, PageView view)
        {
            string cols = @"Title, Content";
            string sqlWhere = "and   FromUser=0 ";
            if (!string.IsNullOrEmpty(search.Title))
            {
                sqlWhere += " Title LIKE '%" + search.Title + "%' ";
            }
            return base.PageGet<U_Information_EX>(view, cols,
                                                  @"U_Information  group by  Title, Content "
                                                  , sqlWhere
                                                  , "",
                                                  "");
        }


        //会员列表分页查询
        public PagedList<Member_EX> QueryMemberList(MemberSearch search, PageView view)
        {
            string cols =
                @"T.[ID],T.[Status],T.[Name],SU.UserName AS UserName,T.[RealName],T.[Sex],T.[Nationality],T.[Maritalstatus],T.[IDType],T.[IDCode],
T.[Education],T.[Mobilenumber],T.[Address],T.[PostCode],T.[Email],T.[Classify] ";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND (T.Name LIKE '%" + search.Name + "%') or (T.Mobilenumber LIKE '%" + search.Name +
                            "%') or (T.Province LIKE '%" + search.Name + "%') or (T.City LIKE '%" + search.Name + "%')";
            }
            if (!string.IsNullOrEmpty(search.Name))
            {
                return base.PageGet<Member_EX>(view, cols,
                    //@"U_Member T left outer join S_Dept s on T.DeptID=s.Id left outer join S_User p on T.CustomerID=p.ID AND  (T.Mobilenumber LIKE '%" + search.Name + "%')"
                                               @"U_Member T join S_User SU on T.ManagerID=SU.ID"
                                               , sqlWhere
                                               , "T.ID DESC ",
                                               "");
            }
            else
            {
                return base.PageGet<Member_EX>(view, cols,
                                               @"U_Member T left outer join S_Dept s on T.DeptID=s.Id  left outer join S_User p on T.CustomerID=p.ID left outer join S_User SU on T.ManagerID=SU.ID"
                                               , sqlWhere
                                               , "T.ID DESC ",
                                               "");
            }
        }

        // 根据客户编号查询显示
        public PagedList<Member_EX> QueryMemberListByUser(MemberSearch search, int userid, PageView view)
        {
            string cols = @" T.[ID]
                          ,T.[Name]
                          ,T.[Password]
                          ,T.[Sex]
                          ,T.[Address]
                          ,T.[PostCode]
                          ,T.[Tel]
                          ,T.[IDCode]
                          ,T.[Email]
                          ,T.[SafeCode]
                          ,T.[RealName]
                          ,T.[Status]
                          ,T.[InputTime]
                          ,T.[Birthday]
                          ,T.[LastLoginTime]
                          ,T.[MemberType]
                          ,T.[AccountLocation]
                          ,T.[Education]
                          ,T.[Maritalstatus]
                          ,T.[Carcase]
                          ,T.[Purchaseconditions]
                          ,T.[Mobilenumber]
                          ,T.[Creditrating]
                          ,T.[ManagerID]
                          ,T.[DeptID]
                          ,T.[CustomerService]
                          ,P.UserName as UserName
                          ,T.[IP]
                          ,T.[Province]  
                          ,T.[City]
                         ,S.[Name] AS DeptService
                            ,(SELECT sum([InCome])
                            FROM [U_FundsFlow ]
                            where memberid=t.id and TYPE =1 and Status = 1) as CumulativeRecharge 
                            ,(SELECT count(id)
                            FROM [T_Investment ]
                            where memberid=t.id ) as TotalBidNumber 
                            ,(SELECT sum([TenderAmt])
                            FROM [T_Investment ]
                            where memberid=t.id ) as TotalBidAmount ";
            string sqlWhere = " AND T.ManagerID = " + userid.ToString() + " ";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND (T.Name LIKE '%" + search.Name + "%')";
            }

            return base.PageGet<Member_EX>(view, cols,
                                           @"U_Member T left join S_Dept s on T.DeptID=s.Id left join S_User p on T.ManagerID=p.ID "
                                           , sqlWhere
                                           , "T.ID DESC ",
                                           "");
        }

        // 根据部门编号查询显示会员列表
        public PagedList<Member_EX> QueryMemberListByDept(MemberSearch search, int deptid, PageView view)
        {
            string cols = @" T.[ID]
                          ,T.[Name]
                          ,T.[Password]
                          ,T.[Sex]
                          ,T.[Address]
                          ,T.[PostCode]
                          ,T.[Tel]
                          ,T.[IDCode]
                          ,T.[Email]
                          ,T.[SafeCode]
                          ,T.[RealName]
                          ,T.[Status]
                          ,T.[InputTime]
                          ,T.[Birthday]
                          ,T.[LastLoginTime]
                          ,T.[MemberType]
                          ,T.[AccountLocation]
                          ,T.[Education]
                          ,T.[Maritalstatus]
                          ,T.[Carcase]
                          ,T.[Purchaseconditions]
                          ,T.[Mobilenumber]
                          ,T.[Creditrating]
                          ,T.[CustomerID]
                          ,T.[DeptID]
                          ,T.[CustomerService]
                          ,P.UserName as UserName
                          ,T.[IP]
                          ,T.[Province]  
                          ,T.[City]
                         ,S.[Name] AS DeptService
                            ,(SELECT sum([InCome])
                            FROM [U_FundsFlow ]
                            where memberid=t.id and TYPE =1 and Status = 1) as CumulativeRecharge 
                            ,(SELECT count(id)
                            FROM [T_Investment ]
                            where memberid=t.id ) as TotalBidNumber 
                            ,(SELECT sum([TenderAmt])
                            FROM [T_Investment ]
                            where memberid=t.id ) as TotalBidAmount ";
            string sqlWhere = " AND T.DeptID = " + deptid.ToString() + " ";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND (T.Name LIKE '%" + search.Name + "%') or (T.Mobilenumber LIKE '%" + search.Name +
                            "%') or (T.Province LIKE '%" + search.Name + "%') or (T.City LIKE '%" + search.Name + "%') ";
            }

            return base.PageGet<Member_EX>(view, cols,
                                           @"U_Member T left join S_Dept s on T.DeptID=s.Id left join S_User p on T.CustomerID=p.ID "
                                           , sqlWhere
                                           , "T.ID DESC ",
                                           "");
        }

        public PagedList<U_Member> QueryAssignMemberList(MemberSearch search, int deptid, PageView view)
        {
            string cols = @" [ID]
                          ,[Name]
                          ,[Password]
                          ,[Sex]
                          ,[Address]
                          ,[PostCode]
                          ,[Tel]
                          ,[IDCode]
                          ,[Email]
                          ,[SafeCode]
                          ,[RealName]
                          ,[Status]
                          ,[InputTime]
                          ,[Birthday]
                          ,[LastLoginTime]
                          ,[MemberType]
                          ,[AccountLocation]
                          ,[Education]
                          ,[Maritalstatus]
                          ,[Carcase]
                          ,[Purchaseconditions]
                          ,[Mobilenumber]
                          ,[Creditrating]
                          ,[CustomerID]
                          ,[CustomerService]
                          ,[DeptID]
                          ,[IP]
                          ,[Province]
                          ,[City]";
            string sqlWhere = " AND isnull(CustomerID,0) = 0 AND DeptID = " + deptid.ToString() + " ";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND (U_Member.Name LIKE '%" + search.Name + "%') or (U_Member.Mobilenumber LIKE '%" +
                            search.Name + "%') or (U_Member.Province LIKE '%" + search.Name +
                            "%') or (U_Member.City LIKE '%" + search.Name + "%') ";
            }

            return base.PageGet<U_Member>(view, cols,
                                          @"U_Member "
                                          , sqlWhere
                                          , "ID DESC ",
                                          "");
        }

        public PagedList<U_Member> QueryDeptAssignMemberList(MemberSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[Name]
                          ,[Password]
                          ,[Sex]
                          ,[Address]
                          ,[PostCode]
                          ,[Tel]
                          ,[IDCode]
                          ,[Email]
                          ,[SafeCode]
                          ,[RealName]
                          ,[Status]
                          ,[InputTime]
                          ,[Birthday]
                          ,[LastLoginTime]
                          ,[MemberType]
                          ,[AccountLocation]
                          ,[Education]
                          ,[Maritalstatus]
                          ,[Carcase]
                          ,[Purchaseconditions]
                          ,[Mobilenumber]
                          ,[Creditrating]
                          ,[CustomerID]
                          ,[CustomerService]
                          ,[IP]
                          ,[Province]
                          ,[City]";
            string sqlWhere = " AND isnull(DeptID,0) = 0 ";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND (U_Member.Name LIKE '%" + search.Name + "%') or (U_Member.Mobilenumber LIKE '%" +
                            search.Name + "%') or (U_Member.Province LIKE '%" + search.Name +
                            "%') or (U_Member.City LIKE '%" + search.Name + "%') ";
            }

            return base.PageGet<U_Member>(view, cols,
                                          @"U_Member "
                                          , sqlWhere
                                          , "ID DESC ",
                                          "");
        }

        public PagedList<U_Member> QueryAssignDeptMemberList(MemberSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[Name]
                          ,[Password]
                          ,[Sex]
                          ,[Address]
                          ,[PostCode]
                          ,[Tel]
                          ,[IDCode]
                          ,[Email]
                          ,[SafeCode]
                          ,[RealName]
                          ,[Status]
                          ,[InputTime]
                          ,[Birthday]
                          ,[LastLoginTime]
                          ,[MemberType]
                          ,[AccountLocation]
                          ,[Education]
                          ,[Maritalstatus]
                          ,[Carcase]
                          ,[Purchaseconditions]
                          ,[Mobilenumber]
                          ,[Creditrating]
                          ,[CustomerID]
                          ,[CustomerService]
                          ,[IP]
                          ,[Province]
                          ,[City]";
            string sqlWhere = " AND isnull(DeptID,0) = 0 ";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND (U_Member.Name LIKE '%" + search.Name + "%') or (U_Member.Mobilenumber LIKE '%" +
                            search.Name + "%') or (U_Member.Province LIKE '%" + search.Name +
                            "%') or (U_Member.City LIKE '%" + search.Name + "%') ";
            }

            return base.PageGet<U_Member>(view, cols,
                                          @"U_Member "
                                          , sqlWhere
                                          , "ID DESC ",
                                          "");
        }

        public int CardVerification(string name, int? IDType, string IDCode, int? id)
        {
            string sql =
                " UPDATE [U_Member] SET RealName=@name , IDType=@IDType , IDCode=@IDCode,IsCardValidate=1  WHERE ID=@ID";
            return base.Excute(sql, new { name = name, IDType = IDType, IDCode = IDCode, ID = id });
        }

        public int MailVerification(string m, int? id)
        {
            string sql = " UPDATE [U_Member] SET IsEmailValidate=1,Email=@m WHERE ID=@id";
            return base.Excute(sql, new { m = m, id = id });
        }

        //分配到部门
        public int AssignDept(string mids, string did)
        {
            string sql = " UPDATE [U_Member] SET [DeptID]=@DeptID WHERE ID in (" + mids + ") ";
            return base.Excute(sql, new { DeptID = did });
        }

        //分配到员工
        public int AssignUser(string mids, string uid)
        {
            string sql = " UPDATE [U_Member] SET [CustomerID] = @UserID WHERE ID in (" + mids + ") ";
            return base.Excute(sql, new { UserID = uid });
        }

        public int AssignMember(string ids, string mid)
        {
            string sql = " UPDATE [U_Member] SET [CustomerID]=@CustomerID WHERE ID in (@id) ";
            return base.Excute(sql, new { id = ids, CustomerID = mid });
        }

        /// <summary>
        /// 删除会员投资信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteMember(int id)
        {
            string sql = " DELETE FROM [U_Member] WHERE ID=@ID";
            return base.Excute(sql, new { ID = id });
        }


        /// <summary>
        /// 检索投资信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Member_EX GetMember(int? id)
        {
            string sql = @"select  [ID]
                          ,[Name]
                          ,[Nickname]
                          ,[Password]
                          ,[Sex]
                          ,[Address]
                          ,[Birthday]
                          ,[PostCode]
                          ,[Tel]
                          ,[IDType]
                          ,[IDCode]
                          ,[IsCardValidate]
                          ,[IsIDValidate]
                          ,[IsEmailValidate]
                          ,[IsBankValidate]
                          ,[IsPhoneValidate]
                          ,[Email]
                          ,[QuestionOne]
                          ,[AnswerOne]
                          ,[SafeCode]
                          ,[RealName]
                          ,[Status]
                          ,[InputTime]
                          ,Convert(char(10),Birthday,126) as BirthdayStr
                          ,[LastLoginTime]
                          ,[MemberType]
                          ,[AccountLocation]
                          ,[Education]
                          ,[Maritalstatus]
                          ,[Carcase]
                          ,[Purchaseconditions]
                          ,[Mobilenumber]
                          ,[Creditrating]
                          ,[CustomerID]
                          ,[CustomerService]
                          ,[IsIdentificate]
                          ,[HeadImage]
                          ,[RegistrationTime]
                          ,[QuestionTwo]
                          ,[AnserTwo]
                          ,[QuestionThree]
                          ,[AnserThree]
                          ,[Nationality]
                          ,[EmergencyContactName]
                          ,[EmergencyContactSex]
                          ,[EmergencyContactIDType]
                          ,[EmergencyContactIDCode]
                          ,[EmergencyContactRelation]
                          ,[EmergencyContactMobileNumber]
                          ,[EmergencyContactAddress]
                          ,[EmergencyContactPostCode]
                          ,[ReceiveFileType]
                          ,[MemberID]
                          ,[ManagerID]
                          ,[RecommendCode]
                            ,[SFTUserName]
                          ,[ClientSource]
,(select CompanyName from U_Company where MemberId=@ID and Status=2)as CompanyName
,( SELECT  SUM(Income)-SUM(OutPay) FROM U_Score WHERE MemberId=@ID)AS Score 
from U_Member where ID=@ID";
            return base.Get<Member_EX>(sql, new { ID = id });
        }

        //获得个人积分
        public int GetMemberScore(int? id)
        {
            string sql = "SELECT  SUM(Income)-SUM(OutPay) FROM U_Score WHERE MemberId=@ID";
            return base.Get<int>(sql, new { ID = id });
        }

        /// <summary>
        /// 根据membername 检索会员信息列表
        /// </summary>
        /// <param name="membername"></param>
        /// <returns></returns>
        public List<Member_EX> GetMemberName(string membername)
        {
            string sql = @"select TOP 5   [ID]
                          ,[Name]
                          ,[Nickname]
                          ,[Password]
                          ,[Sex]
                          ,[Address]
                          ,[PostCode]
                          ,[Tel]
                          ,[IDType]
                          ,[IDCode]
                          ,[IsCardValidate]
                          ,[IsIDValidate]
                          ,[IsBankValidate]
                          ,[Email]
                          ,[QuestionOne]
                          ,[AnswerOne]
                          ,[SafeCode]
                          ,[RealName]
                          ,[Status]
                          ,[InputTime]
                          ,Convert(char(10),Birthday,126) as birthday
                          ,[LastLoginTime]
                          ,[MemberType]
                          ,[AccountLocation]
                          ,[Education]
                          ,[Maritalstatus]
                          ,[Carcase]
                          ,[Purchaseconditions]
                          ,[Mobilenumber]
                          ,[Creditrating]
                          ,[CustomerID]
                          ,[CustomerService]
                          ,[IsIdentificate]
                          ,[HeadImage]
                          ,[RegistrationTime]
                          ,[QuestionTwo]
                          ,[AnserTwo]
                          ,[QuestionThree]
                          ,[AnserThree]  from U_Member where ('@'+Name) like '" + membername + "%'";
            return base.Query<Member_EX>(sql, null);
        }

        //(确定会员名唯一)
        public U_Member GetMemberInfoByName(string name)
        {
            string sql = "select * from U_Member where Nickname=@Nickname";
            return base.Get<U_Member>(sql, new { Nickname = name });
        }



        public int AccountVerification(string AccountName, int? id)
        {
            string sql =
                " INSERT INTO U_Account(MemberId,AccountName,PayPassword,Status,CreateTime) VALUES(@id,@AccountName,NULL,1,GETDATE())";
            return base.Excute(sql, new { id = id, AccountName = AccountName });
        }

        public int Account_BankVerification(int accountid, string Bank, string Branch, string BankCardNo,
                                            string RealNameOfBank)
        {
            string sql =
                " INSERT INTO U_BankCard(AccountId,Branch,BranchDetail,BankCardNo,RealName) VALUES(@accountid,@Branch,@BranchDetail,@BankCardNo,@RealNameOfBank)";
            return base.Excute(sql,
                               new
                                   {
                                       accountid = accountid,
                                       Branch = Branch,
                                       BankCardNo = BankCardNo,
                                       RealNameOfBank = RealNameOfBank,
                                       BranchDetail = Branch
                                   });
        }

        public int BankValidate(int? id)
        {
            string sql = " UPDATE U_Member Set IsBankValidate=1 where ID=@id";
            return base.Excute(sql, new { id = id });
        }

        public int PayValidate(int? id)
        {
            string sql = " UPDATE U_Member Set IsPasswordValidate=1 where ID=@id";
            return base.Excute(sql, new { id = id });
        }

        //验证激活邮箱
        public int SetEmailValidate(string membername, string email)
        {
            string sql = " UPDATE U_Member Set IsEmailValidate=1,Set Email=@Email where Name=@Name";
            return base.Excute(sql, new { Name = membername });
        }

        public int SelAccountID(int? id)
        {
            string sql = " SELECT TOP 1 ID FROM U_Account where MemberId=@id ORDER BY ID DESC";
            return base.Get<int>(sql, new { id = id });
        }

        /// <summary>
        /// 设置安全问题（根据memberId更新安全问题）
        /// </summary>
        /// <param name="membername"></param>
        /// <param name="question"></param>
        /// <param name="anser"></param>
        /// <returns></returns>
        public int SetQuestionOne(int id, string question, string anser)
        {
            string sql = " update U_Member set QuestionOne=@QuestionOne ,AnswerOne=@AnswerOne where ID=@ID";
            return base.Excute(sql, new { ID = id, QuestionOne = question, AnswerOne = anser });
        }

        public int SetQuestionTwo(int id, string question, string anser)
        {
            string sql = "update U_Member set QuestionTwo=@QuestionTwo,AnserTwo=@AnserTwo where ID=@ID";
            return base.Excute(sql, new { ID = id, QuestionTwo = question, AnserTwo = anser });
        }

        public int SetQuestionThree(int id, string question, string anser)
        {
            string sql = "update U_Member set QuestionThree=@QuestionThree,AnserThree=@AnserThree where ID=@ID";
            return base.Excute(sql, new { ID = id, QuestionThree = question, AnserThree = anser });
        }

        //验证支付密码
        public int ValidatePayPass(string memberName, string accountName, string payPwd)
        {
            string sql = @"SELECT COUNT(A.ID) FROM U_Member A
JOIN U_Account B ON A.ID=B.MemberId
WHERE A.Name=@MemberName AND B.AccountName=@AccountName AND B.PayPassword=@PayPassword";
            return base.Get<int>(sql, new { MemberName = memberName, AccountName = accountName, PayPassword = payPwd });
        }

        //修改登录密码
        public int UpdateLoginpwd(int? id, string password)
        {
            string sql = "update U_Member set Password=@Password where ID=@ID";
            return base.Excute(sql, new { ID = id, Password = password });
        }

        //修改支付密码
        public int UpdatePaypwd(int? id, string password)
        {
            string sql = "update U_Account set PayPassword=@PayPassword where MemberId=@ID";
            return base.Excute(sql, new { ID = id, PayPassword = password });
        }

        //我的会员
        public PagedList<U_Member> GetMyMemberList(MemberSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[Name]
                          ,[Password]
                          ,[Sex]
                          ,[Address]
                          ,[PostCode]
                          ,[Tel]
                          ,[IDCode]
                          ,[Email]
                          ,[SafeCode]
                          ,[RealName]
                          ,[Status]
                          ,[InputTime]
                          ,[Birthday]
                          ,[LastLoginTime]
                          ,[MemberType]
                          ,[AccountLocation]
                          ,[Education]
                          ,[Maritalstatus]
                          ,[Carcase]
                          ,[Purchaseconditions]
                          ,[Mobilenumber]
                          ,[Creditrating]
                          ,[CustomerService]
                          ,[IP]
                          ,[Province]
                          ,[City]";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND ((U_Member.Name LIKE '%" + search.Name + "%') or (U_Member.Mobilenumber LIKE '%" +
                            search.Name + "%') or (U_Member.Province LIKE '%" + search.Name +
                            "%') or (U_Member.City LIKE '%" + search.Name + "%')) ";
            }
            if (!string.IsNullOrEmpty(search.MKey))
            {
                sqlWhere += " AND (U_Member.MKey = '" + search.MKey + "') ";
            }
            return base.PageGet<U_Member>(view, cols,
                                          @"U_Member "
                                          , sqlWhere
                                          , "ID DESC ",
                                          "");
        }

        /// <summary>
        /// 会员好友
        /// </summary>
        /// <param name="search"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public PagedList<U_Member> GetMyMemberFriendList(MemberSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[Name]
                          ,[Password]
                          ,[Sex]
                          ,[Address]
                          ,[PostCode]
                          ,[Tel]
                          ,[IDCode]
                          ,[Email]
                          ,[SafeCode]
                          ,[RealName]
                          ,[Status]
                          ,[InputTime]
                          ,[Birthday]
                          ,[LastLoginTime]
                          ,[MemberType]
                          ,[AccountLocation]
                          ,[Education]
                          ,[Maritalstatus]
                          ,[Carcase]
                          ,[Purchaseconditions]
                          ,[Mobilenumber]
                          ,[Creditrating]
                          ,[CustomerService]
                          ,[IP]
                          ,[Province]
                          ,[City]";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND ((U_Member.Name LIKE '%" + search.Name + "%') or (U_Member.Mobilenumber LIKE '%" +
                            search.Name + "%') or (U_Member.Province LIKE '%" + search.Name +
                            "%') or (U_Member.City LIKE '%" + search.Name + "%')) ";
            }
            if (!string.IsNullOrEmpty(search.MemberId))
            {
                sqlWhere += " AND ( ";
                sqlWhere += " ID in (select ApplyFriendMemberID from U_MemberFriend where AcceptFriendMemberID = " +
                            search.MemberId + " And Status = 1) ";
                sqlWhere += " or ID in (select AcceptFriendMemberID from U_MemberFriend where ApplyFriendMemberID = " +
                            search.MemberId + " And Status = 1) ) ";
            }
            return base.PageGet<U_Member>(view, cols,
                                          @"U_Member "
                                          , sqlWhere
                                          , "ID DESC ",
                                          "");
        }

        /// <summary>
        /// 会员好友添加
        /// </summary>
        /// <param name="search"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public PagedList<U_Member> GetMyMemberFriendAddList(MemberSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[Name]
                          ,[Password]
                          ,[Sex]
                          ,[Address]
                          ,[PostCode]
                          ,[Tel]
                          ,[IDCode]
                          ,[Email]
                          ,[SafeCode]
                          ,[RealName]
                          ,[Status]
                          ,[InputTime]
                          ,[Birthday]
                          ,[LastLoginTime]
                          ,[MemberType]
                          ,[AccountLocation]
                          ,[Education]
                          ,[Maritalstatus]
                          ,[Carcase]
                          ,[Purchaseconditions]
                          ,[Mobilenumber]
                          ,[Creditrating]
                          ,[CustomerService]
                          ,[IP]
                          ,[Province]
                          ,[City]";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND (U_Member.Name LIKE '%" + search.Name + "%') or (U_Member.Mobilenumber LIKE '%" +
                            search.Name + "%') or (U_Member.Province LIKE '%" + search.Name +
                            "%') or (U_Member.City LIKE '%" + search.Name + "%')";
            }
            else
            {
                sqlWhere += " AND 1=2 ";
            }
            if (!string.IsNullOrEmpty(search.MemberId))
            {
                sqlWhere += " AND ( ";
                sqlWhere += " ID not in (select ApplyFriendMemberID from U_MemberFriend where AcceptFriendMemberID = " +
                            search.MemberId + " And Status = 1) ";
                sqlWhere +=
                    " and ID not in (select AcceptFriendMemberID from U_MemberFriend where ApplyFriendMemberID = " +
                    search.MemberId + " And Status = 1) ) ";
            }
            return base.PageGet<U_Member>(view, cols,
                                          @"U_Member "
                                          , sqlWhere
                                          , "ID DESC ",
                                          "");
        }

        /// <summary>
        /// 接受会员好友
        /// </summary>
        /// <param name="search"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public PagedList<U_Member> GetMyMemberFriendAcceptList(MemberSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[Name]
                          ,[Password]
                          ,[Sex]
                          ,[Address]
                          ,[PostCode]
                          ,[Tel]
                          ,[IDCode]
                          ,[Email]
                          ,[SafeCode]
                          ,[RealName]
                          ,[Status]
                          ,[InputTime]
                          ,[Birthday]
                          ,[LastLoginTime]
                          ,[MemberType]
                          ,[AccountLocation]
                          ,[Education]
                          ,[Maritalstatus]
                          ,[Carcase]
                          ,[Purchaseconditions]
                          ,[Mobilenumber]
                          ,[Creditrating]
                          ,[CustomerService]
                          ,[IP]
                          ,[Province]
                          ,[City]";
            string sqlWhere = "";

            if (!string.IsNullOrEmpty(search.MemberId))
            {
                sqlWhere += " AND ID in (select ApplyFriendMemberID from U_MemberFriend where AcceptFriendMemberID = " +
                            search.MemberId + " And isnull(Status,0) = 0) ";
            }
            return base.PageGet<U_Member>(view, cols,
                                          @"U_Member "
                                          , sqlWhere
                                          , "ID DESC ",
                                          "");
        }

        /// <summary>
        /// 添加会员好友
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int InsertMemberFriend(int applyfriendmemberid, int acceptfriendmemberid)
        {
            string sql =
                " insert into [U_MemberFriend](ApplyFriendMemberID,AcceptFriendMemberID,Status) values(@ApplyFriendMemberID,@AcceptFriendMemberID,0)";
            return base.Excute(sql,
                               new
                                   {
                                       ApplyFriendMemberID = applyfriendmemberid,
                                       AcceptFriendMemberID = acceptfriendmemberid
                                   });
        }

        /// <summary>
        /// 接受会员好友
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AcceptMemberFriend(int applyfriendmemberid, int acceptfriendmemberid)
        {
            string sql =
                " update [U_MemberFriend] set Status=1 where ApplyFriendMemberID=@ApplyFriendMemberID and AcceptFriendMemberID=@AcceptFriendMemberID ";
            return base.Excute(sql,
                               new
                                   {
                                       ApplyFriendMemberID = applyfriendmemberid,
                                       AcceptFriendMemberID = acceptfriendmemberid
                                   });

        }

        //我的好友
        public List<U_MemberFriend_EX> GetMemberFriends(MemberSearch search)
        {
            string sql = "SELECT top 5 * FROM [U_Member] where ('@'+Name) like '" + search.Name + "%' ";
            //left join U_Member member on memberfriends.ApplyFriendMemberID=member.ID
            //  where ('@'+member.Name) like '" + name + "%' and memberfriends.Status=1";
            if (!string.IsNullOrEmpty(search.MemberId))
            {
                sql += " AND ( ";
                sql += " ID in (select ApplyFriendMemberID from U_MemberFriend where AcceptFriendMemberID = " +
                       search.MemberId + " And Status = 1) ";
                sql += " or ID in (select AcceptFriendMemberID from U_MemberFriend where ApplyFriendMemberID = " +
                       search.MemberId + " And Status = 1) ) ";
            }
            return base.Query<U_MemberFriend_EX>(sql, null);
        }

        /// <summary>
        /// @我的会员(下拉显示我的会员列表Name)
        /// </summary>
        /// <param name="membername"></param>
        /// <param name="mkey"></param>
        /// <returns></returns>
        public List<U_Member> GetMyMemeber(MemberSearch search)
        {
            string sql = @" SELECT top 5 * FROM [U_Member] where 1=1";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sql += " AND ((('@')+U_Member.Name LIKE '" + search.Name + "%')) ";
            }
            if (!string.IsNullOrEmpty(search.MKey))
            {
                sql += " AND (U_Member.MKey = '" + search.MKey + "') ";
            }
            return base.Query<U_Member>(sql, null);
        }

        /// <summary>
        /// 获得我的好友、我的会员数据集
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public List<U_Member> GetAllMemberandFriend(MemberSearch search)
        {
            string sql = @"SELECT  * FROM [U_Member] where ('@'+Name) like '" + search.Name +
                         "%'AND ( ID in (select ApplyFriendMemberID from U_MemberFriend where AcceptFriendMemberID = " +
                         search.MemberId +
                         " And Status = 1) or ID in (select AcceptFriendMemberID from U_MemberFriend where ApplyFriendMemberID = " +
                         search.MemberId +
                         " And Status = 1) ) union SELECT  * FROM [U_Member] where 1=1 AND ((('@')+U_Member.Name LIKE '" +
                         search.Name + "%')) AND (U_Member.MKey = '" + search.MKey + "')";
            return base.Query<U_Member>(sql, null);
        }

        /// <summary>
        /// 获得所有我的好友的MemberId
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public List<U_Member> GetMemberFriendsId(MemberSearch search)
        {
            string sql = "SELECT  * FROM [U_Member] where 1=1";
            //left join U_Member member on memberfriends.ApplyFriendMemberID=member.ID
            //  where ('@'+member.Name) like '" + name + "%' and memberfriends.Status=1";
            if (!string.IsNullOrEmpty(search.MemberId))
            {
                sql += " AND ( ";
                sql += " ID in (select ApplyFriendMemberID from U_MemberFriend where AcceptFriendMemberID = " +
                       search.MemberId + " And Status = 1) ";
                sql += " or ID in (select AcceptFriendMemberID from U_MemberFriend where ApplyFriendMemberID = " +
                       search.MemberId + " And Status = 1) ) ";
            }
            return base.Query<U_Member>(sql, null);
        }

        /// <summary>
        /// 获得所有我的会员的MemberId
        /// </summary>
        /// <param name="mkey"></param>
        /// <returns></returns>
        public List<U_Member> GetMyMemberId(string mkey)
        {
            string sql = "SELECT * FROM [U_Member] WHERE MKey=@MKey";
            return base.Query<U_Member>(sql, new { MKey = mkey });
        }

        /// <summary>
        /// 获得所有MemeberId
        /// </summary>
        /// <param name="memberId"></param>
        /// <param name="mkey"></param>
        /// <returns></returns>
        public List<U_Member> GetAllId(string memberId, string mkey)
        {
            string sql = @"SELECT  * FROM [U_Member] where 
                           (ID in (select ApplyFriendMemberID from U_MemberFriend where AcceptFriendMemberID = " +
                         memberId +
                         " And Status = 1) or ID in (select AcceptFriendMemberID from U_MemberFriend where ApplyFriendMemberID = " +
                         memberId +
                         " And Status = 1) ) union SELECT  * FROM [SHLY].[dbo].[U_Member] where(U_Member.MKey = '" +
                         mkey + "')";
            return base.Query<U_Member>(sql, null);

        }

        /// <summary>
        /// 检索所有会员（用户）的ID
        /// </summary>
        /// <returns></returns>
        public List<U_Member> GetID()
        {
            string sql = "select ID from U_Member";
            return base.Query<U_Member>(sql, null);
        }

        //留言审核
        public int MessageAudit(int id, string audituser, int status, string datetime, string auditremark)
        {
            string sql =
                "update U_Message set [Status] =@Status, AuditUser=@AuditUser, AuditTime=@AuditTime, AuditExplain=@AuditExplain where id=@id";
            return base.Excute(sql,
                               new
                                   {
                                       ID = id,
                                       AuditUser = audituser,
                                       Status = status,
                                       AuditTime = datetime,
                                       AuditExplain = auditremark
                                   });

        }

        //查询出当天日期注册的最后一个会员
        public Member_EX SelectLastMemberToday(string date)
        {
            string sql = "select top 1 MemberID from U_Member where MemberID like @MemberID order by ID desc";
            return base.Get<Member_EX>(sql, new { MemberID = "%" + date + "%" });
        }

        //查询省份证是否重复
        public int ValicateIDCodeRepeat(string idCode)
        {
            string sql = "select count(*) from U_Member where IDType=1 and IDCode=@IDCode";
            return base.Get<int>(sql, new { IDCode = idCode });
        }

        //会员统计
        public PagedList<U_MemberStatistics_EX> QueryMemberStatisticsList(MemberStatisticsSearch search, PageView view)
        {
            string cols = @" ROW_NUMBER() over(order by t.时间) as OrderNumber,
       CAST(CAST(t.新增充值会员数 AS float)/t.新增会员数 AS numeric(10,1)) as RechargePercent ,
	   CAST(CAST(t.登陆会员数 AS float)/t.累计会员数 AS numeric(10,2)) as MemberActivity ,
	   t.新增会员数 as NewMember,
	   t.新增充值会员数 as NewPrepaidMember,
	   t.时间 as myTime,
	   t.累计会员数 as MemberCount ";
            string sqlWhere = "";
            if (search.MyTime != null)
            {
                sqlWhere += " AND t.时间 = " + "'" + search.MyTime + "'";
            }

            return base.PageGet<U_MemberStatistics_EX>(view, cols,
                //@"U_Member T left outer join S_Dept s on T.DeptID=s.Id left outer join S_User p on T.CustomerID=p.ID AND  (T.Mobilenumber LIKE '%" + search.Name + "%')"
                                                       @"(select CONVERT(char(10),RegistrationTime,120)as 时间 , 
	COUNT(*) as 新增会员数,
	(select COUNT(*) from U_FundsFlow where CONVERT(char(10),CreateTime,120) in 
		(select CONVERT(char(10),RegistrationTime,120) from U_Member) and CONVERT(char(10),CreateTime,120)=CONVERT(char(10),RegistrationTime,120)) as 新增充值会员数 ,
			
	(select COUNT(*) from U_MemberLoginLog where CONVERT(char(10),LoginTime,120) in 
		(select CONVERT(char(10),RegistrationTime,120) from U_Member) and CONVERT(char(10),LoginTime,120)=CONVERT(char(10),RegistrationTime,120)) as 登陆会员数,
	(select COUNT(*) from U_Member) as 累计会员数
from U_Member 
group by CONVERT(char(10),RegistrationTime,120)) as t"
                                                       , sqlWhere
                                                       , "t.时间 asc",
                                                       "");
        }

        //查询省份证是否重复
        public int ValicateIDCodeRepeat(string idCode, int id)
        {
            if (id == -1)
            {
                string sql = "select count(*) from U_Member where IDType=1 and IDCode=@IDCode";
                return base.Get<int>(sql, new { IDCode = idCode });
            }
            else
            {
                string sql = "select count(*) from U_Member where IDType=1 and IDCode=@IDCode and ID<>@ID";
                return base.Get<int>(sql, new { IDCode = idCode, ID = id });
            }
        }

        //查询手机号码是否重复
        public int ValidateMobilePhoneRepeat(string mobilePhone, int id)
        {
            if (id == -1)
            {
                string sql = "select count(*) from U_Member where Mobilenumber = @Mobilenumber";
                return base.Get<int>(sql, new { Mobilenumber = mobilePhone });
            }
            else
            {
                string sql = "select count(*) from U_Member where Mobilenumber = @Mobilenumber and ID <>@ID";
                return base.Get<int>(sql, new { Mobilenumber = mobilePhone, ID = id });
            }
        }

        //查询邮箱是否重复
        public int ValidateEmailRepeat(string email, int id)
        {
            if (id == -1)
            {
                string sql = "select count(*) from U_Member where Email=@Email";
                return base.Get<int>(sql, new { Email = email });
            }
            else
            {
                string sql = "select count(*) from U_Member where Email=@Email and ID <>@ID";
                return base.Get<int>(sql, new { Email = email, ID = id });
            }

        }

        //登陆
        public U_Member login(string userName, string userPwd)
        {
            string sql = "select * from U_Member where Name=@Name and Password=@Password";
            return base.Get<U_Member>(sql, new { Name = userName, Password = userPwd });
        }

        //使用邮箱登陆
        public U_Member LoginByEmail(string userName, string userPwd)
        {
            string sql =
                @"select * from U_Member 
                           where Email=@Email and Password=@Password";
            return base.Get<U_Member>(sql, new { Email = userName, Password = userPwd });
        }
        //使用手机号码登陆
        public U_Member LoginByPhone(string userName, string userPwd)
        {
            string sql =
                @"select * from U_Member 
                           where Mobilenumber=@Mobilenumber and Password=@Password";
            return base.Get<U_Member>(sql, new { Mobilenumber = userName, Password = userPwd });
        }

        //查询用户名是否存在
        public string CheckUserName(string userName)
        {
            string sql = "select count(*) from U_Member where Name=@Name";
            int result = base.Get<int>(sql, new { Name = userName });
            if (result >= 1)
            {
                return "you";
            }
            else
            {
                return "wu";
            }
        }


        //查询该会员是否已经绑定了这张银行卡
        public int CheckBankCardDangDing(int memberId, string cardNo)
        {
            string sql = "select count(*) from U_BankCard where MemberId = @MemberId and CardNO=@CardNO";
            return base.Get<int>(sql, new { MemberId = memberId, CardNO = cardNo });
        }

        //修改会员状态
        //public int updateMemberStatus(int? id, int status)
        //{
        //    string sql = "update U_Member set Status=@status where ID=@ID";
        //    return base.Excute(sql, new { ID = id, Status = status });
        //}

        //根据会员ID查询会员信息
        public U_Member getMemberInfo(int? id)
        {
            string sql = "select * from U_Member where ID=@ID";
            return base.Get<U_Member>(sql, new { ID = id });
        }
        //根据用户名、邮箱、手机号、查询用户信息（忘记密码）
        public U_Member ForgetPwd(string info)
        {
            string sql = "select * from U_Member where Email=@Email or Mobilenumber=@Mobilenumber";
            return base.Get<U_Member>(sql, new { Email = info, Mobilenumber = info });
        }



        //根据邮箱查询
        public U_Member getMemberStatus(string email)
        {
            string sql = "select * from U_Member where Email=@Email";
            return base.Get<U_Member>(sql, new { Email = email });
        }



        public bool ExistsPhone(string phpone)
        {
            const string sql = "select count(*) from U_Member where Mobilenumber=@Mobilenumber";
            return base.Get<int>(sql, new { Mobilenumber = phpone }) == 1;
        }

        public int ValidatePhone(U_Member member)
        {
            string sql = "update U_Member set IsPhoneValidate=1,Mobilenumber=@Phone where ID=@ID";
            return base.Excute(sql, new { Phone = member.Mobilenumber, ID = member.ID });
        }

        public U_Member GetModelByName(string name)
        {
            string sql = "select * from U_Member where Name=@Name";
            return base.Get<U_Member>(sql, new { Name = name });
        }

        public U_Member GetModelBySftUserName(string sftUserName)
        {
            string sql = @"select [ID],[Name],[Password],[Sex],[Address],[PostCode],[Tel],[IDType],[IDCode],[IsCardValidate],[IsIDValidate],[IsEmailValidate],[IsBankValidate]
                                ,[IsPhoneValidate],[IsPasswordValidate],[QuestionOne],[AnswerOne],[SafeCode],[RealName],[Status],[InputTime],[Birthday],[LastLoginTime]
                                ,[MemberType],[AccountLocation],[Education],[Maritalstatus],[Carcase],[Purchaseconditions],[Mobilenumber],[Creditrating],[DeptID],[CustomerID],[CustomerService]
                                ,[IsIdentificate],[HeadImage],[RegistrationTime],[QuestionTwo],[AnserTwo],[QuestionThree],[AnserThree],[IP],[Province],[City]
                                ,[MKey],[Email],[SFTUserName],[SFTUserId],[YibaoId],[Nationality],[EmergencyContactName],[EmergencyContactSex],[EmergencyContactIDType]
                                ,[EmergencyContactIDCode],[EmergencyContactRelation],[EmergencyContactMobileNumber],[EmergencyContactAddress]
                                ,[EmergencyContactPostCode],[ReceiveFileType],[MemberID],[ManagerID],[RecommendCode],[ClientSource]
                                ,[Classify] 
                        from U_Member where SFTUserName=@SFTUserName";
            return base.Get<U_Member>(sql, new { SFTUserName = sftUserName });
        }
        //根据公司ID查询项目发起人信息
        public U_CompanyAnnex_EX GetMemberInfo(int? cid)
        {
            string sql = "select [RealName],[CompanyName],[Adress],[ManageAdree] from U_Member m left join U_Company c on m.ID=c.MemberId where c.Id=@ID";
            return base.Get<U_CompanyAnnex_EX>(sql, new { ID = cid });
        }

        public int SaveMemberOauth(U_Member_oauth menOauth)
        {
            if (menOauth.id > 0)
            {
                return base.Update(menOauth);
            }
            else
            {
                return (int)base.Insert(menOauth);
            }
        }

        public U_Member_oauth OauthLogin(string oauth_name, string oauth_openid)
        {
            string sql = "select top 1 id,user_id,user_name,oauth_name,oauth_access_token,oauth_openid,add_time from U_Member_oauth where oauth_name=@oauth_name and oauth_openid=@oauth_openid";
            return base.Get<U_Member_oauth>(sql, new { oauth_name = oauth_name, oauth_openid = oauth_openid });
        }
    }
}
